package ee.android.kinokavad.data.db;

import java.util.ArrayList;
import java.util.List;

import org.joda.time.DateTime;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDoneException;
import android.database.sqlite.SQLiteStatement;
import ee.android.kinokavad.App;
import ee.android.kinokavad.data.Cinema;
import ee.android.kinokavad.data.Movie;
import ee.android.kinokavad.data.Session;
import ee.android.kinokavad.utils.LogUtils.Logger;

public class DBController {

	private static final String TAG = DBController.class.getSimpleName();
	// Database fields
	private SQLiteDatabase database;
	private SQLiteHelper dbHelper;
	private int databaseUsers;

	private String[] ALL_CINEMA_COLUMNS = { SQLiteHelper.COLUMN_CINEMA_ID,  SQLiteHelper.COLUMN_CINEMA_NETID, SQLiteHelper.COLUMN_CINEMA_NAME,
			SQLiteHelper.COLUMN_CINEMA_CITY, SQLiteHelper.COLUMN_CINEMA_SELECTED, SQLiteHelper.COLUMN_CINEMA_PHONE,
			SQLiteHelper.COLUMN_CINEMA_EMAIL, SQLiteHelper.COLUMN_CINEMA_ADDRESS, SQLiteHelper.COLUMN_CINEMA_LAT,
			SQLiteHelper.COLUMN_CINEMA_LON, SQLiteHelper.COLUMN_CINEMA_INFO, SQLiteHelper.COLUMN_CINEMA_HOMEPAGE};
	private String[] ALL_MOVIE_COLUMNS = { SQLiteHelper.COLUMN_MOVIE_ID,SQLiteHelper.COLUMN_MOVIE_NETID, 
			SQLiteHelper.COLUMN_MOVIE_TITLE, SQLiteHelper.COLUMN_MOVIE_ORIGINAL_TITLE,
			SQLiteHelper.COLUMN_MOVIE_DESCRIPTION, SQLiteHelper.COLUMN_MOVIE_GENRE,
			SQLiteHelper.COLUMN_MOVIE_YOUTUBE, SQLiteHelper.COLUMN_MOVIE_IMDB,
			SQLiteHelper.COLUMN_MOVIE_PICTURE, SQLiteHelper.COLUMN_MOVIE_POSTER, SQLiteHelper.COLUMN_MOVIE_RATING, SQLiteHelper.COLUMN_MOVIE_DURATION};
	private String[] ALL_SESSION_COLUMNS = { SQLiteHelper.COLUMN_SESSION_ID, SQLiteHelper.COLUMN_SESSION_CINEMAID,
			SQLiteHelper.COLUMN_SESSION_MOVIEID, SQLiteHelper.COLUMN_SESSION_DATETIME, SQLiteHelper.COLUMN_SESSION_PICTURE};


	public DBController(Context context) {
		dbHelper = new SQLiteHelper(context);
	}

	public void addCinemaToDatabase(Cinema cinema) {
		ContentValues values = new ContentValues();
		values.put(SQLiteHelper.COLUMN_CINEMA_NETID, cinema.getNetId());
		values.put(SQLiteHelper.COLUMN_CINEMA_NAME, cinema.getName());
		values.put(SQLiteHelper.COLUMN_CINEMA_CITY, cinema.getCity());

		values.put(SQLiteHelper.COLUMN_CINEMA_PHONE, cinema.getPhone());
		values.put(SQLiteHelper.COLUMN_CINEMA_EMAIL, cinema.getEmail());
		values.put(SQLiteHelper.COLUMN_CINEMA_ADDRESS, cinema.getAddress());
		values.put(SQLiteHelper.COLUMN_CINEMA_LAT, cinema.getLat());
		values.put(SQLiteHelper.COLUMN_CINEMA_LON, cinema.getLon());
		values.put(SQLiteHelper.COLUMN_CINEMA_INFO, cinema.getInfo());
		values.put(SQLiteHelper.COLUMN_CINEMA_HOMEPAGE, cinema.getHomePage());
		int selected =  cinema.isSelected() ? 1:0;
		values.put(SQLiteHelper.COLUMN_CINEMA_SELECTED, selected);

		database.insert(SQLiteHelper.TABLE_CINEMAS, null, values);
	}

	public void updateCinema(Cinema cinema) {
		ContentValues values = new ContentValues();

		int selected =  cinema.isSelected() ? 1:0;
		values.put(SQLiteHelper.COLUMN_CINEMA_SELECTED, selected);

		database.update(SQLiteHelper.TABLE_CINEMAS, values, SQLiteHelper.COLUMN_CINEMA_NETID +"="+ cinema.getNetId(), null);

	}


	public List<Cinema> loadAllCinemasFromDatabase() {
		List<Cinema> cinemas = new ArrayList<Cinema>();
		String orderBy = "CASE "+SQLiteHelper.COLUMN_CINEMA_CITY + " WHEN 'Tallinn' then 0 when 'Tartu' then 1 else 2 END";
		Cursor cursor = database.query(SQLiteHelper.TABLE_CINEMAS,
				ALL_CINEMA_COLUMNS, null, null, null, null, orderBy);

		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Cinema cinema = cursorToCinema(cursor);
			cinemas.add(cinema);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		return cinemas;
	}

	private Cinema cursorToCinema(Cursor cursor) {
		Cinema cinema = new Cinema(cursor.getLong(0), cursor.getLong(1), cursor.getString(2), 
				cursor.getString(3), cursor.getLong(4) == 1, 
				cursor.getString(5), cursor.getString(6), cursor.getString(7), 
				cursor.getDouble(8), cursor.getDouble(9), 
				cursor.getString(10), cursor.getString(11));
		return cinema;
	}


	public void addMovieToDatabase(Movie movie) {
		ContentValues values = new ContentValues();
		values.put(SQLiteHelper.COLUMN_MOVIE_NETID, movie.getNetId());

		if (movie.getTitle() != null)
			values.put(SQLiteHelper.COLUMN_MOVIE_TITLE, movie.getTitle());
		if (movie.getOriginalTitle() != null)
				values.put(SQLiteHelper.COLUMN_MOVIE_ORIGINAL_TITLE, movie.getOriginalTitle());
		if (movie.getDescription() != null)
			values.put(SQLiteHelper.COLUMN_MOVIE_DESCRIPTION, movie.getDescription());
		if (movie.getGenre() != null)
			values.put(SQLiteHelper.COLUMN_MOVIE_GENRE, movie.getGenre());
		if (movie.getYoutubeLink() != null)
			values.put(SQLiteHelper.COLUMN_MOVIE_YOUTUBE,movie.getYoutubeLink());
		if (movie.getImdbLink() != null)
			values.put(SQLiteHelper.COLUMN_MOVIE_IMDB, movie.getImdbLink());
		if (movie.getPictureLink() != null)
			values.put(SQLiteHelper.COLUMN_MOVIE_PICTURE, movie.getPictureLink());

		database.insert(SQLiteHelper.TABLE_MOVIES, null, values);
	}
	
	public void updateMovie(Movie movie) {
		
		ContentValues values = new ContentValues();
		if (movie.getPoster() != null)
			values.put(SQLiteHelper.COLUMN_MOVIE_POSTER, movie.getPoster());
		if (movie.getImdbRating() != null)
			values.put(SQLiteHelper.COLUMN_MOVIE_RATING, movie.getImdbRating());
		if (movie.getDuration() != null)
			values.put(SQLiteHelper.COLUMN_MOVIE_DURATION, movie.getDuration());

		database.update(SQLiteHelper.TABLE_MOVIES, values, SQLiteHelper.COLUMN_MOVIE_NETID +"="+ movie.getNetId(), null);
		
	}

	public Movie loadMovieByNetId(long movieNetId) {
		Movie movie = null;
		Cursor cursor = database.query(SQLiteHelper.TABLE_MOVIES,
				ALL_MOVIE_COLUMNS, SQLiteHelper.COLUMN_MOVIE_NETID + "=" + movieNetId, null, null, null, null);
		if (cursor.moveToFirst()) {
			movie = cursorToMovie(cursor);
		}
		cursor.close();
		return movie;
	}

	public List<Movie> loadAllMoviesFromDatabase() {
		List<Movie> movies = new ArrayList<Movie>();
		Cursor cursor = database.query(SQLiteHelper.TABLE_MOVIES,
				ALL_MOVIE_COLUMNS, null, null, null, null, null);

		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Movie movie = cursorToMovie(cursor);
			movies.add(movie);
			cursor.moveToNext();
		}
		cursor.close();
		return movies;
	}

	public List<Long> loadAllMoviesIdsFromDatabase() {
		List<Long> movieIds = new ArrayList<Long>();
		Cursor cursor = database.query(SQLiteHelper.TABLE_MOVIES,
				new String[] {SQLiteHelper.COLUMN_MOVIE_NETID}, null, null, null, null, null);

		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			movieIds.add(cursor.getLong(0));
			cursor.moveToNext();
		}
		cursor.close();
		return movieIds;
	}

	private Movie cursorToMovie(Cursor cursor) {
		Movie movie = new Movie(
				cursor.getLong(0),		//id 
				cursor.getLong(1),		//net id
				cursor.getString(2), 	//title
				cursor.getString(3),	//original title
				cursor.getString(4), 	//description
				cursor.getString(5), 	//genre
				cursor.getString(6), 	//youtube link
				cursor.getString(7),	//imdb link
				cursor.getString(8),	//picture link
				cursor.getString(9), 	//poster link
				cursor.getString(10),	//imdb rating
				cursor.getString(11)	//duration
				);
		return movie;
	}



	public void addSessionToDatabase(Session session) {
		ContentValues values = new ContentValues();
		values.put(SQLiteHelper.COLUMN_SESSION_CINEMAID, session.getCinemaId());
		values.put(SQLiteHelper.COLUMN_SESSION_MOVIEID, session.getMovieId());
		values.put(SQLiteHelper.COLUMN_SESSION_DATETIME, session.getDateTime());
		values.put(SQLiteHelper.COLUMN_SESSION_PICTURE, session.getPictureLink());

		database.insert(SQLiteHelper.TABLE_SESSIONS, null, values);
	}

	public List<Session> loadSessionsForCinemaAndDate(long cinemaId, DateTime dateTime) {
		String date = dateTime.toString("yyyy-MM-dd");
		
		List<Session> sessions = new ArrayList<Session>();
		
		String[] columns = {
				SQLiteHelper.TABLE_SESSIONS + "." + SQLiteHelper.COLUMN_SESSION_ID,
				SQLiteHelper.COLUMN_SESSION_CINEMAID, SQLiteHelper.COLUMN_SESSION_MOVIEID, 
				SQLiteHelper.COLUMN_SESSION_DATETIME, 
				SQLiteHelper.TABLE_SESSIONS + "." + SQLiteHelper.COLUMN_SESSION_PICTURE,
				SQLiteHelper.COLUMN_MOVIE_TITLE, SQLiteHelper.COLUMN_MOVIE_DURATION,
				SQLiteHelper.COLUMN_MOVIE_GENRE};
		
		String tables = String.format("%s,%s", SQLiteHelper.TABLE_SESSIONS, SQLiteHelper.TABLE_MOVIES);
		String where = String.format("%s = ? AND %s = %s AND date(%s) = date(?)",
				SQLiteHelper.COLUMN_SESSION_CINEMAID, 
				SQLiteHelper.COLUMN_MOVIE_NETID, 
				SQLiteHelper.COLUMN_SESSION_MOVIEID,
				SQLiteHelper.COLUMN_SESSION_DATETIME
				);
		String [] whereArgs = {
				String.valueOf(cinemaId),
				date
		};
		
		String orderBy = String.format("datetime(%s) ASC", SQLiteHelper.COLUMN_SESSION_DATETIME);
		
		Cursor cursor = database.query(tables, columns, where, whereArgs, null, null, orderBy);

		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Session session = cursorToSessionWithMovie(cursor);
			sessions.add(session);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		return sessions;
	}

	public DateTime loadMaxDateFromSessions() {

		DateTime date = DateTime.now();
		String orderString = "date("+SQLiteHelper.COLUMN_SESSION_DATETIME+") DESC";
		Cursor c = database.query(SQLiteHelper.TABLE_SESSIONS, new String [] {SQLiteHelper.COLUMN_SESSION_DATETIME}, null, null, null, null, orderString, "1");
		if (c.moveToFirst()) {
			date = new DateTime(c.getString(0)).withTime(23, 59, 59, 999);
		}
		c.close();
		return date;
	}

	public long getSessionCount() {

		long count = 0;
		SQLiteStatement s = database.compileStatement("select count("+SQLiteHelper.COLUMN_SESSION_ID+") from "+SQLiteHelper.TABLE_SESSIONS+"" +
				" where date("+SQLiteHelper.COLUMN_SESSION_DATETIME+") >= date('now');");
		try {
			count = s.simpleQueryForLong();
		} catch (SQLiteDoneException e) {
		}
		s.close();
		
		return count;

	}

	public long getCinemaCount() {

		long count = 0;
		SQLiteStatement s = database.compileStatement("select count("+SQLiteHelper.COLUMN_CINEMA_ID+") from "+SQLiteHelper.TABLE_CINEMAS+";");
		try {
			count = s.simpleQueryForLong();
		} catch (SQLiteDoneException e) {
		}
		s.close();
		
		return count;

	}

	public String getMovieName(long id) {

		String movieName = "";
		SQLiteStatement s = database.compileStatement("select "+SQLiteHelper.COLUMN_MOVIE_TITLE+" from "+SQLiteHelper.TABLE_MOVIES+" where " +
				SQLiteHelper.COLUMN_MOVIE_NETID + " = " + id + ";");
		try {
			movieName = s.simpleQueryForString();
		} catch (SQLiteDoneException e) {
		}
		s.close();

		return movieName;
	}
	
	private Session cursorToSession(Cursor c) {
		Session session = new Session(
				c.getLong(0), 		//id
				c.getLong(1), 		//cinemaId
				c.getLong(2),		//movieId
				c.getString(3), 	//datetime
				c.getString(4)
				);
		
		return session;
	}

	private Session cursorToSessionWithMovie(Cursor c) {
		Session session = new Session(
				c.getLong(0), 		//id
				c.getLong(1), 		//cinemaId
				c.getLong(2),		//movieId
				c.getString(3), 	//datetime
				c.getString(4), 	//picture
				c.getString(5), 	//movie title
				c.getString(6), 	//duration
				c.getString(7)		//movie genre
				);
		
		return session;
	}

	public void deleteOldSessions() {
		String where = "date(" + SQLiteHelper.COLUMN_SESSION_DATETIME + ") < date('now')";
		long deleted = database.delete(SQLiteHelper.TABLE_SESSIONS, where, null);
		if (App.LOCAL_LOG)
			Logger.d(TAG, "Deleted sessions: " + deleted);
	}

	public void deleteOldMovies() {
		String where = SQLiteHelper.COLUMN_MOVIE_NETID + " NOT IN (SELECT DISTINCT " + SQLiteHelper.COLUMN_SESSION_MOVIEID + " FROM " + SQLiteHelper.TABLE_SESSIONS + ")";
		long deleted = database.delete(SQLiteHelper.TABLE_MOVIES, where, null);
		if (App.LOCAL_LOG)
			Logger.d(TAG, "Deleted movies: " + deleted);
	}

	public void deleteAllMovies() {
		database.delete(SQLiteHelper.TABLE_MOVIES, null, null);
	}

	public void deleteAllCinemas() {
		database.delete(SQLiteHelper.TABLE_CINEMAS, null, null);
	}

	public void deleteAllSessions() {
		database.delete(SQLiteHelper.TABLE_SESSIONS, null, null);
	}


	/**
	 * Tries to open database if it is not yet open. Increases the number of database users to avoid conflicts with multiple threads.
	 * @throws SQLException
	 */
	public void open() throws SQLException {
		databaseUsers++;
		if (App.LOCAL_LOG)
			Logger.d("OPEN_USERS", databaseUsers + "");
		if (database == null || !database.isOpen())
			database = dbHelper.getWritableDatabase();
	}

	/**
	 * Tries too open database and begins transaction.
	 */
	public void begin() {
		open();
		database.beginTransaction();
	}

	/**
	 * Sets transaction successful.
	 */
	public void success() {
		database.setTransactionSuccessful();
	}

	/**
	 * Ends transaction, tries to close database.
	 */
	public void end() {
		database.endTransaction();
		closeRequest();
	}

	/**
	 * Closes database if no database users are found and database is open
	 */
	public void closeRequest() {
		databaseUsers--;
		if (App.LOCAL_LOG)
			Logger.d("CLOSE_USERS", databaseUsers + "");
		if(database.isOpen() && databaseUsers==0) {
			dbHelper.close();
		}
		
	}

	public boolean isOpen() {
		return database.isOpen();
	}

	public List<Session> loadSessionsForMovie(long movieId) {
		
		List<Session> sessions = new ArrayList<Session>();
		
		String where = SQLiteHelper.COLUMN_SESSION_MOVIEID + "=" + movieId + " AND datetime(" + SQLiteHelper.COLUMN_SESSION_DATETIME + ") >= datetime('now', 'localtime')";
		
		Cursor cursor = database.query(SQLiteHelper.TABLE_SESSIONS,
				ALL_SESSION_COLUMNS, where, null, null, null, "date("+SQLiteHelper.COLUMN_SESSION_DATETIME+")");

		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Session session = cursorToSession(cursor);
			sessions.add(session);
			cursor.moveToNext();
		}
		cursor.close();
		
		return sessions;
	}

	
	public Cinema loadCinemaById(long id) {
		Cinema cinema = null;
		
		Cursor cursor = database.query(SQLiteHelper.TABLE_CINEMAS,
				ALL_CINEMA_COLUMNS, SQLiteHelper.COLUMN_CINEMA_NETID + "=" + id, null, null, null, null);
		if (cursor.moveToFirst()) {
			cinema = cursorToCinema(cursor);
		}
		cursor.close();
		return cinema;
	}

}
